/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2015-06-22 23:05:41                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('fault') and o.name = 'FK_FAULT_FAULT_USE_USER')
alter table fault
   drop constraint FK_FAULT_FAULT_USE_USER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('reason') and o.name = 'FK_REASON_FAULT_REA_FAULT')
alter table reason
   drop constraint FK_REASON_FAULT_REA_FAULT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('department')
            and   type = 'U')
   drop table department
go

if exists (select 1
            from  sysobjects
           where  id = object_id('fault')
            and   type = 'U')
   drop table fault
go

if exists (select 1
            from  sysobjects
           where  id = object_id('positiontype')
            and   type = 'U')
   drop table positiontype
go

if exists (select 1
            from  sysobjects
           where  id = object_id('reason')
            and   type = 'U')
   drop table reason
go

if exists (select 1
            from  sysobjects
           where  id = object_id('subsystem')
            and   type = 'U')
   drop table subsystem
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"user"')
            and   type = 'U')
   drop table "user"
go

/*==============================================================*/
/* Table: department                                            */
/*==============================================================*/
create table department (
   ID                   int                  not null,
   bname                varchar(20)          null,
   phone                varchar(20)          null,
   sjbm                 varchar(20)          null,
   ip                   varchar(20)          null,
   bmjb                 int                  null,
   usernum              int                  null,
   constraint PK_DEPARTMENT primary key (ID)
)
go

/*==============================================================*/
/* Table: fault                                                 */
/*==============================================================*/
create table fault (
   id                   int                  not null,
   user_id              int                  null,
   fname                varchar(50)          null,
   frombusiness         varchar(10)          null,
   symptom              varchar(200)         null,
   happentime           datetime             null,
   service              varchar(50)          null,
   results              varchar(50)          null,
   jltime               datetime             null,
   fip                  varchar(20)          null,
   phone                varchar(20)          null,
   mark                 int                  null,
   constraint PK_FAULT primary key (id)
)
go

/*==============================================================*/
/* Table: positiontype                                          */
/*==============================================================*/
create table positiontype (
   ID                   int                  not null,
   pname                varchar(20)          null,
   qx                   int                  null,
   superqx              int                  null,
   definedqx            int                  null,
   department           varchar(20)          null,
   constraint PK_POSITIONTYPE primary key (ID)
)
go

/*==============================================================*/
/* Table: reason                                                */
/*==============================================================*/
create table reason (
   id                   int                  not null,
   fault_id             int                  null,
   rname                varchar(30)          null,
   describe             varchar(200)         null,
   dealwith             varchar(150)         null,
   constraint PK_REASON primary key (id)
)
go

/*==============================================================*/
/* Table: subsystem                                             */
/*==============================================================*/
create table subsystem (
   id                   int                  not null,
   sname                varchar(30)          null,
   describe             varchar(200)         null,
   fault                varchar(50)          null,
   others               varchar(100)         null,
   constraint PK_SUBSYSTEM primary key (id)
)
go

/*==============================================================*/
/* Table: "user"                                                */
/*==============================================================*/
create table "user" (
   id                   int                  not null,
   role                 char                 null,
   realname             varchar(30)          null,
   uname                varchar(20)          null,
   passward             varchar(20)          null,
   position             varchar(20)          null,
   department           varchar(100)         null,
   phone                varchar(22)          null,
   zbjs                 int                  null,
   constraint PK_USER primary key (id)
)
go

alter table fault
   add constraint FK_FAULT_FAULT_USE_USER foreign key (user_id)
      references "user" (id)
go

alter table reason
   add constraint FK_REASON_FAULT_REA_FAULT foreign key (fault_id)
      references fault (id)
go

